{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# eICU Collaborative Research Database\n",
    "\n",
    "# Notebook 2: Demographics and severity of illness in a single patient\n",
    "\n",
    "The aim of this notebook is to introduce high level admission details relating to a single patient stay, using the following tables:\n",
    "\n",
    "- `patient`\n",
    "- `admissiondx`\n",
    "- `apacheapsvar`\n",
    "- `apachepredvar`\n",
    "- `apachepatientresult`\n",
    "\n",
    "Before starting, you will need to copy the eicu demo database file ('eicu_demo.sqlite3') to the `data` directory.\n",
    "\n",
    "Documentation on the eICU Collaborative Research Database can be found at: http://eicu-crd.mit.edu/."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Getting set up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import psycopg2\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Plot settings\n",
    "%matplotlib inline\n",
    "plt.style.use('ggplot')\n",
    "fontsize = 20 # size for x and y ticks\n",
    "plt.rcParams['legend.fontsize'] = fontsize\n",
    "plt.rcParams.update({'font.size': fontsize})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to the database - which is assumed to be in the current directory\n",
    "fn = 'eicu_demo.sqlite3'\n",
    "con = sqlite3.connect(fn)\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Display a list of tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query = \\\n",
    "\"\"\"\n",
    "SELECT type, name\n",
    "FROM sqlite_master \n",
    "WHERE type='table'\n",
    "ORDER BY name;\n",
    "\"\"\"\n",
    "\n",
    "list_of_tables = pd.read_sql_query(query,con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "list_of_tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Selecting a single patient stay "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1. The `patient` table\n",
    "\n",
    "The `patient` table includes general information about the patient admissions (for example, demographics, admission and discharge details). See: http://eicu-crd.mit.edu/eicutables/patient/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "Use your knowledge from the previous notebook and the online documentation (http://eicu-crd.mit.edu/) to answer the following questions:\n",
    "\n",
    "- Which column in the `patient` table is distinct for each stay in the ICU (similar to `icustay_id` in MIMIC-III)?\n",
    "- Which column is unique for each patient (similar to `subject_id` in MIMIC-III)?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# select a single ICU stay\n",
    "patientunitstayid = 141296"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM patient\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# run the query and assign the output to a variable\n",
    "patient = pd.read_sql_query(query,con)\n",
    "patient.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# display a complete list of columns\n",
    "patient.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# select a limited number of columns to view\n",
    "columns = ['uniquepid','patientunitstayid','gender','age','unitdischargestatus']\n",
    "patient[columns]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "- What year was the patient admitted to the ICU? Which year was he or she discharged?\n",
    "- What was the status of the patient upon discharge from the unit?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 3.2. The `admissiondx` table\n",
    "\n",
    "The `admissiondx` table contains the primary diagnosis for admission to the ICU according to the APACHE scoring criteria. For more detail, see: http://eicu-crd.mit.edu/eicutables/admissiondx/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM admissiondx\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# run the query and assign the output to a variable\n",
    "admissiondx = pd.read_sql_query(query,con)\n",
    "admissiondx.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "admissiondx.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "- What was the primary reason for admission?\n",
    "- How soon after admission to the ICU was the diagnoses recorded in eCareManager?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 3.3. The `apacheapsvar` table\n",
    "\n",
    "The `apacheapsvar` table contains the variables used to calculate the Acute Physiology Score (APS) III for patients. APS-III is an established method of summarizing patient severity of illness on admission to the ICU. \n",
    "\n",
    "The score is part of the Acute Physiology Age Chronic Health Evaluation (APACHE) system of equations for predicting outcomes for ICU patients. See: http://eicu-crd.mit.edu/eicutables/apacheApsVar/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM apacheapsvar\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# run the query and assign the output to a variable\n",
    "apacheapsvar = pd.read_sql_query(query,con)\n",
    "apacheapsvar.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "apacheapsvar.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "- What was the 'worst' heart rate recorded for the patient during the scoring period?\n",
    "- Was the patient oriented and able to converse normally on the day of admission? (hint: the `verbal` element refers to the Glasgow Coma Scale).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4. The `apachepredvar` table\n",
    "\n",
    "The `apachepredvar` table provides variables underlying the APACHE predictions. Acute Physiology Age Chronic Health Evaluation (APACHE) consists of a groups of equations used for predicting outcomes in critically ill patients. See: http://eicu-crd.mit.edu/eicutables/apachePredVar/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM apachepredvar\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# run the query and assign the output to a variable\n",
    "apachepredvar = pd.read_sql_query(query,con)\n",
    "apachepredvar.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "apachepredvar.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "apachepredvar.ventday1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "- Was the patient ventilated during (APACHE) day 1 of their stay?\n",
    "- Did the patient have diabetes?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 3.5. The `apachepatientresult` table\n",
    "\n",
    "The `apachepatientresult` table provides predictions made by the APACHE score (versions IV and IVa), including probability of mortality, length of stay, and ventilation days. See: http://eicu-crd.mit.edu/eicutables/apachePatientResult/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM apachepatientresult\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# run the query and assign the output to a variable\n",
    "apachepatientresult = pd.read_sql_query(query,con)\n",
    "apachepatientresult.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "apachepatientresult.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "- What versions of the APACHE score are computed?\n",
    "- How many days during the stay was the patient ventilated?\n",
    "- How long was the patient predicted to stay in hospital?\n",
    "- Was this prediction close to the truth?"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
